/**
 * 项目名：五位一体化工安全信息平台
 * 日期：    2019-11-24 23:34:51
 * Copyright (c) 2015- joysuch-版权所有
 */

package com.joysuch.wwyt.bp.repository;

import com.joysuch.wwyt.bp.entity.BpDepart;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * BpDepartDao
 */
public interface BpDepartDao extends JpaRepository<BpDepart, Long>, QuerydslPredicateExecutor<BpDepart> {
    public Page<BpDepart> findAll(Specification<BpDepart> spec, Pageable pageable);

    public BpDepart save(BpDepart bean);

    public void delete(BpDepart bean);

    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND code = ?1", nativeQuery = true)
    public BpDepart findFirstByCode(String code);

    @Query("select name from BpDepart where id = ?1")
    public String findDepartNameById(Long id);

    @Query("select name from BpDepart where id = ?1")
    public String getNameById(Long id);

    //    @Query("select id from BpDepart where code like ?1%")
    @Query(value = "SELECT id FROM wwyt_bp_depart WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND code LIKE :code%", nativeQuery = true)
    public List<Long> findSubDepartIdsByCode(@Param("code") String code);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND code LIKE :code%", nativeQuery = true)
//    @Query("select m from BpDepart m where code like ?1%")
    public List<BpDepart> findSubDepartByCode(@Param("code") String code);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND name =?1", nativeQuery = true)
    public BpDepart findFirstByName(String name);

    @Query(value = "SELECT id FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND parent_id =?1", nativeQuery = true)
//    @Query("select id from BpDepart where parentId =:parentId")
    List<Long> findIdByByParentId(Long parentId);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE PARENT_ID = ?1 and delete_flag='0' AND (show_state IS NULL OR show_state!=44)", nativeQuery = true)
    List<BpDepart> findByParentId(Long parentId);

    @Query("select parentId from BpDepart where id =:id")
    Long findParentIdById(Long id);

    @Query(value = "SELECT id FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44)AND name =?1 limit 1", nativeQuery = true)
//    @Query("select id from BpDepart where name = ?1")
    Long findIdByName(String name);

    @Query(value = "SELECT id FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44)AND name like concat('%',?1,'%') ", nativeQuery = true)
//    @Query("select id from BpDepart where name = ?1")
    List<Long> findIdByNameLike(String name);

    @Query(value = "SELECT id FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND code =?1", nativeQuery = true)
//    @Query("select id from BpDepart where code = ?1")
    Long findIdByCode(String departCode);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND tenent_id = ?1", nativeQuery = true)
//    @Query(value = "select * from wwyt_bp_depart where tenent_id = ?1 and delete_flag='0' ", nativeQuery = true)
    List<BpDepart> findAllDepartByTenentId(Long tenentId);

    @Query(value = "select chief_id from wwyt_bp_depart where id =:id", nativeQuery = true)
    Long findChiefIdById(Long id);


    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND code LIKE CONCAT(?1,'%') AND code !=?1", nativeQuery = true)
//    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE CODE LIKE :code% AND DELETE_FLAG = '0'", nativeQuery = true)
    List<BpDepart> findLikeCode(String code);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44)", nativeQuery = true)
    public List<BpDepart> findAll();

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) and org_code = ?1 and  tenent_id =?2 and parent_id is null", nativeQuery = true)
    BpDepart getFatherDepart(String orgCode, Long tenantId);

    @Query(value = "SELECT COUNT(ID) FROM wwyt_bp_depart  WHERE code =?1 and delete_flag='0' AND (show_state IS NULL OR show_state!=44) ", nativeQuery = true)
    int countByCodeAndState(String code);


    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE PARENT_ID is null AND delete_flag='0' AND (show_state IS NULL OR show_state!=44) ", nativeQuery = true)
    List<BpDepart> findByParentIsNull();

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND id in (?1)", nativeQuery = true)
    List<BpDepart> findByIdIn(List<Long> deptIds);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE   id in (?1)", nativeQuery = true)
    List<BpDepart> findByIdInFO(List<Long> deptIds);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND id in (?1)", nativeQuery = true)
    List<BpDepart> findCodeListByDeptIds(List<Long> deptIds);

    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE name =?1  and parent_id =?2 and id !=?3 and delete_flag='0' AND (show_state IS NULL OR show_state!=44)", nativeQuery = true)
    List<BpDepart> findByNameAndParentIdAndIdNotEq(String name, Long id, Long id1);

    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE locate_sync = 0 and PARENT_ID is null AND delete_flag='0' AND (show_state IS NULL OR show_state!=44) ", nativeQuery = true)
    List<BpDepart> findByLocateSyncAndParentIsNull();

    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE name =?1  and parent_id =?2 and delete_flag='0' AND (show_state IS NULL OR show_state!=44)", nativeQuery = true)
    List<BpDepart> findByNameAndParentId(String departName, Long pId);

    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE unique_id =?1  and delete_flag='0' AND (show_state IS NULL OR show_state!=44) limit 1", nativeQuery = true)
    BpDepart findByUniqueId(String unqiueId);

    @Query(value = "SELECT * FROM wwyt_bp_depart WHERE ID =?1  and delete_flag='0' AND (show_state IS NULL OR show_state!=44) limit 1", nativeQuery = true)
    BpDepart findByIdAndStatus(Long parentId);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE PARENT_ID = (select ID from wwyt_bp_depart where unique_id = ?1 and delete_flag='0' AND (show_state IS NULL OR show_state!=44)) ", nativeQuery = true)
    List<BpDepart> findByParentUniqueId(String uniqueId);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND name =?1 AND ORG_CODE = ?2 AND TENENT_ID = ?3", nativeQuery = true)
    BpDepart findFirstByNameAndCurrentCompany(String depart, String orgCode, Long tenantId);

    @Query(value = "select name from `wwyt_bp_depart` where delete_flag = '0'and (show_state IS NULL OR show_state!=44) group by name having count(*)>1", nativeQuery = true)
    List<String> getDuplicateDepartByName();

    @Query(value = "select distinct(d.id) from `wwyt_bp_depart` d left join `wwyt_base_user` u on d.`ID` = u.`DEPART_ID` where d.`DELETE_FLAG` = '0' AND (d.show_state IS NULL OR d.show_state!=44) AND u.`ID` in ?1",nativeQuery = true)
    List<Long> getDepartIdsByUserIds(List<Long> otherUserIds);

    @Query(value = "select distinct(d.id) from `wwyt_bp_depart` d left join `wwyt_base_user` u on d.`ID` = u.`DEPART_ID` where d.`DELETE_FLAG` = '0' AND (d.show_state IS NULL OR d.show_state!=44) AND u.`JOB` in ?1",nativeQuery = true)
    List<Long> getDepartIdsByJobIds(List<Long> otherJobIds);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE id in (?1)", nativeQuery = true)
    List<BpDepart> findByIds(List<Long> departIds);

//    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND code LIKE ?1% AND id!=", nativeQuery = true)
//    List<BpDepart> findLikeCodeAndIdNotEq(@Param("code")String code,@Param("departId")Long departId);

    @Query(value = "SELECT id FROM wwyt_bp_depart  WHERE delete_flag='0' AND (show_state IS NULL OR show_state!=44) AND name like concat('%',?1,'%') ",nativeQuery = true)
    List<Long> getIdByNameLike(String name);

    @Query(value = "SELECT * FROM wwyt_bp_depart  WHERE CHIEF_ID = ?1 AND  delete_flag='0'", nativeQuery = true)
    List<BpDepart> findByUserId(Long userId);
}
